﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class EventInfoProvider : PhotoGallery.Core.Providers.DALProvider.IEventInfoProvider
    {
        #region Sql
        private static readonly string SqlSelectEventInfo = "SELECT [Id], [Root],[Name],[Description] FROM [EventInfo] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertEventInfo = "INSERT INTO [EventInfo]([Root],[Name],[Description]) values(@Root,@Name,@Description);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateEventInfo = "UPDATE [EventInfo] SET [Root] = @Root,[Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteEventInfo = "DELETE [EventInfo] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllEventInfo = "SELECT [Id], [Root],[Name],[Description] FROM [EventInfo] order by root;";


        private static readonly string SqlGetAllEventInfoOrder = "";
        #endregion

        #region Procedures
        /*

-- Begin of EventInfo
IF OBJECT_ID(N'[USP_EventInfo_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_EventInfo_Delete_By_Id]

IF OBJECT_ID(N'[USP_EventInfo_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_EventInfo_Select_By_Id]

IF OBJECT_ID(N'[USP_EventInfo_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_EventInfo_Insert]

IF OBJECT_ID(N'[USP_EventInfo_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_EventInfo_Update_By_Id]

IF OBJECT_ID(N'[USP_EventInfo_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_EventInfo_SelectAll]

GO

--region [USP_EventInfo_Delete_By_Id]
CREATE PROCEDURE [USP_EventInfo_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [EventInfo] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_EventInfo_Select_By_Id]
CREATE PROCEDURE [USP_EventInfo_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Root],[Name],[Description] FROM [EventInfo] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_EventInfo_Insert]
CREATE PROCEDURE [USP_EventInfo_Insert]
	@Root	nvarchar(100),
	@Name	nvarchar(50),
	@Description	nvarchar(100)
AS
SET NOCOUNT ON
INSERT INTO [EventInfo]([Root],[Name],[Description]) values(@Root,@Name,@Description);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_EventInfo_Update_By_Id]
CREATE PROCEDURE [USP_EventInfo_Update_By_Id]
	@Root	nvarchar(100),
	@Name	nvarchar(50),
	@Description	nvarchar(100),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [EventInfo] SET [Root] = @Root,[Name] = @Name,[Description] = @Description WHERE  [Id]=@Id;
--endregion
GO

--region [USP_EventInfo_SelectAll]
CREATE PROCEDURE [USP_EventInfo_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Root],[Name],[Description] FROM [EventInfo];
--endregion
GO


-- End of EventInfo
*/
        #endregion

        #region IEventInfoProvider Members
        public Core.Business.EventInfo Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectEventInfo);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.EventInfo eventInfo = new Core.Business.EventInfo();

                if (!reader.IsDBNull(0)) eventInfo.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) eventInfo.Root = reader.GetString(1);
                if (!reader.IsDBNull(2)) eventInfo.Name = reader.GetString(2);
                if (!reader.IsDBNull(3)) eventInfo.Description = reader.GetString(3);

                reader.Close();
                return eventInfo;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.EventInfo eventInfo)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Root", SqlDbType.NVarChar, eventInfo.Root);
            sql.AddParameter("@Name", SqlDbType.NVarChar, eventInfo.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, eventInfo.Description);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertEventInfo);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) eventInfo.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.EventInfo eventInfo)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Root", SqlDbType.NVarChar, eventInfo.Root);
            sql.AddParameter("@Name", SqlDbType.NVarChar, eventInfo.Name);
            sql.AddParameter("@Description", SqlDbType.NVarChar, eventInfo.Description);
            sql.AddParameter("@Id", SqlDbType.Int, eventInfo.Id);
            sql.ExecuteSql(SqlUpdateEventInfo);
        }

        public void Delete(Core.Business.EventInfo eventInfo)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, eventInfo.Id);
            sql.ExecuteSql(SqlDeleteEventInfo);
        }

        public IList<Core.Business.EventInfo> GetAllEventInfo()
        {
            IList<Core.Business.EventInfo> eventInfolist = new List<Core.Business.EventInfo>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllEventInfo);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.EventInfo eventInfo = new Core.Business.EventInfo();

                    if (!reader.IsDBNull(0)) eventInfo.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) eventInfo.Root = reader.GetString(1);
                    if (!reader.IsDBNull(2)) eventInfo.Name = reader.GetString(2);
                    if (!reader.IsDBNull(3)) eventInfo.Description = reader.GetString(3);

                    eventInfo.MarkOld();
                    eventInfolist.Add(eventInfo);
                }
                reader.Close();
            }
            return eventInfolist;
        }
        #endregion


        public ICollection<EventInfo> GetAllOrder()
        {
            return this.GetAllEventInfo();
        }
    }
}
